{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "young-spelling",
   "metadata": {},
   "source": [
    "# Lesson 1 - 创建df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "impaired-toronto",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 读取csv文件\n",
    "df = pd.read_csv('pokemon_data.csv')\n",
    "\n",
    "# 读取文本文件，需要制定分隔符，其中\\t表示tab键\n",
    "# df = pd.read_csv('pokemon_data.txt', delimiter='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "alternate-piano",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 确保已经安装了openpyxl\n",
    "df = pd.read_excel('pokemon_data.xlsx', engine='openpyxl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "stuck-twenty",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 用字典创建\n",
    "data = {'学号':[9527, 9528, 9529, 9530],\n",
    "        '姓名':['张三', '李四', '王五', '麦叔'],\n",
    "        '语文':[98, 87, 95, 68],\n",
    "        '数学':[78, 96, 89, 99],\n",
    "        'Python':[88, 100, 99, 97]\n",
    "       }\n",
    "df = pd.DataFrame(data)\n",
    "df\n",
    "\n",
    "# 用numpy创建测试数据\n",
    "import numpy as np\n",
    "df = pd.DataFrame(np.random.randn(120).reshape((6, 20)))\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "scheduled-roads",
   "metadata": {},
   "source": [
    "# Lesson2 选取数据快捷方法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "duplicate-democracy",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 3个快捷方法\n",
    "df.head() #默认读取前5行\n",
    "df.head(10) #指定读取行数\n",
    "df.tail() #默认读取后5行\n",
    "df.tail(10) #指定读取行数\n",
    "df.sample() #随机选取1条\n",
    "df.sample(3) #随机选取n条"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "british-witness",
   "metadata": {},
   "source": [
    "## 1. 方括号的用法：可以读行，也可以读列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "heard-investor",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('pokemon_data.csv')\n",
    "\n",
    "# 通过切片方式读取行\n",
    "df[1:5]\n",
    "\n",
    "# 读取所有行\n",
    "df[:]\n",
    "\n",
    "# 下面这行代码会报错，因为方括号默认是用来读取某一列的，而没有5这一列\n",
    "# df[5]\n",
    "# 读取列\n",
    "df['体力']\n",
    "\n",
    "# 读取多列\n",
    "df[['名字', '体力']]\n",
    "\n",
    "# 行列混合\n",
    "df[1:5][['名字', '体力']]\n",
    "\n",
    "# 列表"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "received-picnic",
   "metadata": {},
   "source": [
    "## 2. 用属性名读取列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "danish-tuning",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 通过属性名读取，但只适合没有空格的情况\n",
    "df.名字\n",
    "\n",
    "# 下面会报错\n",
    "#df.Type 1\n",
    "\n",
    "# 所以最好还是用方括号\n",
    "df['Type 1']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "imperial-break",
   "metadata": {},
   "outputs": [],
   "source": [
    "# iloc\n",
    "\n",
    "# 读取特定行\n",
    "df.iloc[1]\n",
    "\n",
    "# 读取行的范围\n",
    "df.iloc[1:-2]\n",
    "\n",
    "# 读取行和列\n",
    "# 下面的代码会报错，因为iloc只接受整数类型\n",
    "# df.iloc[:, '名字']\n",
    "# 读取1列\n",
    "df.iloc[1:5, 2]\n",
    "\n",
    "# 读取多列\n",
    "df.iloc[1:5, [2,5,8]]\n",
    "\n",
    "# 根据切片读取列\n",
    "df.iloc[1:5, 2:8]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "tracked-evans",
   "metadata": {},
   "outputs": [],
   "source": [
    "# loc是根据索引读取，可以不是数字下标，这个在索引部分会再深入学习\n",
    "# 这里行的索引就是1，2，3，4，5这样的数字，但列的索引是列的名字。行的索引也可以修改为不是数字。\n",
    "\n",
    "df.loc[1:5, ['名字', '体力']]\n",
    "\n",
    "# 下面的会报错，因为loc是按名字来的\n",
    "# df.loc[1:5, 1:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "recognized-egyptian",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 循环\n",
    "for index, r in df.iterrows():\n",
    "    print(index, r.体力)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "weekly-telephone",
   "metadata": {},
   "source": [
    "# Lesson 3 过滤和排序"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "specific-welding",
   "metadata": {},
   "source": [
    "## 1. 用方括号"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "facial-english",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('pokemon_data.csv')\n",
    "\n",
    "# 单个过滤条件\n",
    "df[df['Type 1'] == 'Grass']\n",
    "df[df['体力'] > 80]\n",
    "\n",
    "# 多个过滤条件：用 | 和&，不能用and和or。注意每个条件都要用小括号括起来。\n",
    "df[(df['体力'] > 80) & (df['防守'] >= 80)]\n",
    "df[(df['体力'] > 80) | (df['防守'] >= 100)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "potential-revolution",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 根据字符串过滤，同样可以使用多个条件\n",
    "df[df['名字'].str.contains('saur')]\n",
    "\n",
    "# 使用非~\n",
    "df[~df['名字'].str.contains('saur')]\n",
    "\n",
    "# 使用正则表达式\n",
    "df[df['Type 1'].str.contains('Fire|Rock', regex=True)]\n",
    "\n",
    "# 使用正则表达式，忽略大小写\n",
    "import re\n",
    "df[df['Type 1'].str.contains('fire|Rock', flags=re.I, regex=True)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "extra-bermuda",
   "metadata": {},
   "source": [
    "## 2. 用loc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "corrected-ratio",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('pokemon_data.csv')\n",
    "\n",
    "# 使用loc可以做同样的过滤，并且可以修改\n",
    "df.loc[df['名字'].str.contains('saur')]\n",
    "\n",
    "# 用loc同时修改，注意逗号用法，讲解看视频\n",
    "df.loc[df['名字'].str.contains('saur'), 'Type 2'] = '麦叔'\n",
    "\n",
    "# 同时修改两列\n",
    "df.loc[df['名字'].str.contains('saur'), ['Type 1','Type 2']] = '麦叔'\n",
    "\n",
    "# 用不同的值修改两列\n",
    "df.loc[df['名字'].str.contains('saur'), ['Type 1','Type 2']] = ['麦叔1','麦叔2']\n",
    "\n",
    "df\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "balanced-reasoning",
   "metadata": {},
   "source": [
    "## 3. 排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "extended-australia",
   "metadata": {},
   "outputs": [],
   "source": [
    "#sort \n",
    "\n",
    "data = {'学号':[9527, 9528, 9529, 9530],\n",
    "        '姓名':['张三', '李四', '王五', '麦叔'],\n",
    "        '语文':[98, 87, 98, 68],\n",
    "        '数学':[78, 96, 89, 99],\n",
    "        'Python':[88, 100, 99, 97]\n",
    "       }\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# 根据索引排序\n",
    "df.sort_index()\n",
    "\n",
    "# 指定倒着排序\n",
    "df.sort_index(ascending=False)\n",
    "\n",
    "# 根据某一列排序\n",
    "df.sort_values('语文', ascending=False)\n",
    "\n",
    "# 根据某多列排序\n",
    "df.sort_values(['语文', '数学'], ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "decimal-twins",
   "metadata": {},
   "source": [
    "# Lesson4 - 统计方法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "overall-stable",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {'学号':[9527, 9528, 9529, 9530],\n",
    "        '姓名':['张三', '李四', '王五', '麦叔'],\n",
    "        '语文':[98, 87, 98, 68],\n",
    "        '数学':[78, 96, 89, 99],\n",
    "        'Python':[88, 100, 99, 97]\n",
    "       }\n",
    "df = pd.DataFrame(data)\n",
    "print(df)\n",
    "\n",
    "# 给每一列排名，返回每个数字在列中的顺序\n",
    "df.rank() \n",
    "\n",
    "# 只给数字列排名，姓名没有排名\n",
    "df.rank(numeric_only = True) \n",
    "\n",
    "# 只给语文排名\n",
    "df.语文.rank()\n",
    "\n",
    "# 添加排名列\n",
    "df['语文排名'] = df.语文.rank()\n",
    "\n",
    "# 指定排名方法\n",
    "df['语文排名'] = df.语文.rank(method='min', ascending=False)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "changed-theta",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "blank-minister",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "german-container",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bottom-webmaster",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.min()\n",
    "type(df.min())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "potential-truth",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sum() #汇总每一列的值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "lovely-registration",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.cumsum() #计算每一列的累加值"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "criminal-cradle",
   "metadata": {},
   "source": [
    "## 2. 属性"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "impressed-execution",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns #列信息\n",
    "df.shape #形状：几行，几列\n",
    "df.size #行x列，总的元素个数\n",
    "df.values #返回值数组\n",
    "df.dtypes # 每一列的数据类型\n",
    "df.ndim #维度\n",
    "df.T  #行列互换，转置"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "floral-apartment",
   "metadata": {},
   "source": [
    "# Lesson 5 修改和排序"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "champion-favor",
   "metadata": {},
   "source": [
    "## 1. 修改数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "viral-commodity",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('pokemon_data.csv')\n",
    "\n",
    "# 修改数据就是使用赋值，我们可以用多种方法修改数据\n",
    "\n",
    "# 修改单个数据\n",
    "df.iloc[0, 1] = '麦叔'\n",
    "\n",
    "# 修改选中的数据：把体力大于60的记录的攻击改成100\n",
    "df.loc[df.体力>60, '攻击'] = 100\n",
    "\n",
    "# 修改多列\n",
    "df.loc[df.体力>60, ['攻击','防守']] = 100\n",
    "\n",
    "# 给不同的列赋予不同的值\n",
    "df.loc[df.体力>60,['攻击','防守']] = [100, 80]\n",
    "\n",
    "#给每个格子赋予不同的值\n",
    "df.loc[df.名字.str.contains('saur'), '体力'] = [88, 99, 77] \n",
    "# df.loc[df.名字.str.contains('saur')]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "empty-latex",
   "metadata": {},
   "source": [
    "## 2. 添加列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "accepted-optimum",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 添加常量值，这个有时候也很有用\n",
    "df['total'] = 100\n",
    "\n",
    "# 添加计算出来的值\n",
    "df['total'] = df['体力'] + df['攻击']\n",
    "\n",
    "# 另一种计算方法\n",
    "df['total'] = df.loc[:, '体力':'速度'].sum(axis=1)\n",
    "\n",
    "# 也可以这样写\n",
    "df['total'] = df.iloc[:, 4:10].sum(axis=1)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "suspected-gallery",
   "metadata": {},
   "source": [
    "## 3. 删除行和列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "natural-there",
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop不是原地删除，如果要原地删除，需要加inplace = True\n",
    "\n",
    "# 删除行\n",
    "df1 = df.drop(0)\n",
    "\n",
    "# 原地删除\n",
    "# df.drop(0, inplace=True)\n",
    "\n",
    "\n",
    "# 删除列，默认是删除行，如果要删除列，需要加上axis=1\n",
    "df1 = df.drop('名字', axis=1)\n",
    "\n",
    "\n",
    "# 删除多行和列\n",
    "df1 = df.drop([1,3,5,7])\n",
    "\n",
    "\n",
    "# 根据条件删除\n",
    "index = df.index[df['Type 1'] == 'Grass']\n",
    "df1 = df.drop(index)\n",
    "\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "environmental-nylon",
   "metadata": {},
   "source": [
    "## 4. 把total放在前面"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "occupied-gathering",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('pokemon_data.csv')\n",
    "df['total'] = 100\n",
    "\n",
    "# 创建表示新的列名顺序的列表\n",
    "cols = ['total'] + df.columns.to_list()[0:-1]\n",
    "df = df[cols]\n",
    "\n",
    "#练习：把total加在体力前，先删掉原来的，多一次练习，可以不删除\n",
    "df.drop('total', axis=1, inplace=True)\n",
    "\n",
    "# 重新添加\n",
    "df['total'] = df.iloc[:, 4:10].sum(axis=1)\n",
    "cols = df.columns.to_list()[0:4] + ['total'] + df.columns.to_list()[4:-1]\n",
    "df = df[cols]\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "infinite-guarantee",
   "metadata": {},
   "source": [
    "## 5. 保存"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "above-sandwich",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('pokemon_data.csv')\n",
    "df['total'] = df.iloc[:, 4:10].sum(axis=1)\n",
    "cols = df.columns.to_list()[0:4] + ['total'] + df.columns.to_list()[4:-1]\n",
    "df = df[cols]\n",
    "\n",
    "df.to_csv('new_file.csv')\n",
    "df.to_excel('new_file.xslx', engine='openpyxl')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "exterior-stage",
   "metadata": {},
   "source": [
    "# Lesson 6 - 索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "interior-boring",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "interested-gabriel",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.applymap(lambda x:f\"{x}2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "communist-dutch",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['count'] = 1\n",
    "df.groupby(['Type 1']).count()\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "united-driver",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
